package logdbservice

import (
	"fmt"
	"strings"
	"time"
	"yunj/pkg/global"
	"yunj/pkg/yunj/util"
)

// 数据表准备（默认创建当前月和下一月的数据表）
// suffix 不需要包含前缀"_"
func TablePrepare(suffix ...string) (err error) {
	// 获取想要添加的数据表明
	tableNamePrefix := fmt.Sprintf("%sadmin_member_log", global.Config.Database.TablePrefix)
	attrTableNamePrefix := fmt.Sprintf("%sadmin_member_log_attr", global.Config.Database.TablePrefix)
	tableNames := []string{}
	attrTableNames := []string{}
	if len(suffix) > 0 {
		for _, s := range suffix {
			if !util.IsValidTableName(s) {
				err = fmt.Errorf("表名称后缀只能包含字母、数字和下划线！")
				return
			}
			tableName := tableNamePrefix
			attrTableName := attrTableNamePrefix
			if !strings.HasPrefix(s, "_") {
				tableName += "_"
				attrTableName += "_"
			}
			tableName += s
			attrTableName += s
			tableNames = append(tableNames, tableName)
			attrTableNames = append(attrTableNames, attrTableName)
		}
	} else {
		tableNames = []string{
			fmt.Sprintf("%s_%s", tableNamePrefix, time.Now().Format("200601")),                  // 当前月份
			fmt.Sprintf("%s_%s", tableNamePrefix, time.Now().AddDate(0, 1, 0).Format("200601")), // 下一个月
		}
		attrTableNames = []string{
			fmt.Sprintf("%s_%s", attrTableNamePrefix, time.Now().Format("200601")),                  // 当前月份
			fmt.Sprintf("%s_%s", attrTableNamePrefix, time.Now().AddDate(0, 1, 0).Format("200601")), // 下一个月
		}
	}
	for _, tableName := range tableNames {
		if err = global.MySQL.Exec(TableSql(tableName)).Error; err != nil {
			err = fmt.Errorf("创建日志表%s失败！%v", tableName, err)
			return
		}
	}
	for _, attrTableName := range attrTableNames {
		if err = global.MySQL.Exec(AttrTableSql(attrTableName)).Error; err != nil {
			err = fmt.Errorf("创建日志属性表%s失败！%v", attrTableName, err)
			return
		}
	}
	return
}

// 日志表sql
func TableSql(table string) string {
	return fmt.Sprintf(`CREATE TABLE IF NOT EXISTS %s  (
						id bigint UNSIGNED NOT NULL,
						auth_key varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '请求权限key',
						url varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '请求地址',
						method varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '请求method',
						member_id int UNSIGNED NOT NULL DEFAULT 0 COMMENT '管理员id',
						data_id varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '数据id',
						ip varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'ip',
						ip_region varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'ip所属区域信息',
						ip_province varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'ip所属省份',
						platform varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '系统平台',
						platform_version varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '系统平台版本号',
						browser varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '浏览器',
						browser_version varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '浏览器版本号',
						created_at datetime NOT NULL,
						latency bigint UNSIGNED NOT NULL DEFAULT 0 COMMENT '耗时（单位：ms）',
						PRIMARY KEY (id) USING BTREE,
						INDEX idx_ip(ip ASC) USING BTREE,
						INDEX idx_member_id(member_id ASC) USING BTREE,
						INDEX idx_data_id(data_id ASC) USING BTREE,
  						INDEX idx_url (url ASC) USING BTREE
						) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '后台成员日志表' ROW_FORMAT = DYNAMIC;`, table)
}

// 日志属性表sql
func AttrTableSql(table string) string {
	// %[2]s 表示第二个参数（即反引号 ），用于包裹 desc` 字段
	return fmt.Sprintf(`CREATE TABLE IF NOT EXISTS %s  (
						log_id bigint UNSIGNED NOT NULL,
						user_agent text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '用户Agent',
						request_params text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '请求参数',
						response_content text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '响应内容',
						%[2]sdesc%[2]s text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '描述',
						PRIMARY KEY (log_id) USING BTREE
						) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '后台成员日志属性表' ROW_FORMAT = DYNAMIC;`, table, "`")
}
